package AdminController.AdminCheck;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import AdminController.AdminExe.AdminExeBean;
import Connect.connector;
import MemberController.MembersBean;
import MypageController.MyWrite.MyWriteBean;

public class AdminCheckDAO {
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public int mappingCount(){
		conn = connector.getConnection();
		try{

			String sql = "select count(*) from MAPPING where Map_Check = 2";
			
			pstmt = conn.prepareStatement( sql );
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				int commentcount = rs.getInt("count(*)");
				return commentcount;
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return -1;
	}
	
	public AdminCheckBean[] mappingList(int number, int size){
		conn = connector.getConnection();
		ArrayList<AdminCheckBean> result = new ArrayList<AdminCheckBean>();	
		int startnum;
		try{
			
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select * from (select * from MAPPING where Map_Check = 2 ORDER BY Map_Date DESC) a where a.Map_Date limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, startnum);
			pstmt.setInt(2, size);
			rs = pstmt.executeQuery();
			
			//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			while( rs.next()){
				AdminCheckBean admincheckbena = new AdminCheckBean();
				
				admincheckbena.setMap_Old(rs.getInt("Map_Old"));
				admincheckbena.setMap_Young(rs.getInt("Map_Young"));
				admincheckbena.setMap_Date(rs.getDate("Map_Date"));
				
				result.add(admincheckbena);
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminCheckBean[0]);
	}
	
	public MembersBean mappingMemberData(int id){
		conn = connector.getConnection();
		MembersBean membersbean = new MembersBean();
		try{
			
			String sql = "select * from MEMBERS where ID = ?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			
			//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			while( rs.next()){
				
				membersbean.setMem_Nick(rs.getString("Mem_Nick"));
				membersbean.setMem_NameH(rs.getString("Mem_NameH"));
				membersbean.setMem_NameK(rs.getString("Mem_NameK"));
				
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return membersbean;
	}
	

	public AdminExeBean[] ChildList(int id){
		conn = connector.getConnection();
		ArrayList<AdminExeBean> list = new ArrayList<AdminExeBean>();
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select ID,Babyplus_Id, Bbp_NameK, Bbp_NameH, Bbp_Birth, Bbp_Gender, Bbp_Photo" );
			sql.append(	" from BABYPHOTO " );				
			sql.append( " where Bbp_Members = ? " );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();

			SimpleDateFormat check = new SimpleDateFormat("yyyy-MM-dd");
			
			while(rs.next()){
				
				AdminExeBean aeyBean = new AdminExeBean();
				aeyBean.setID(rs.getInt(1));
				aeyBean.setBabyplus_Id(rs.getInt(2));
				aeyBean.setBbp_Namek(rs.getString(3));
				aeyBean.setBbp_NameH(rs.getString(4));
				aeyBean.setBbp_Birth(check.format(rs.getTimestamp(5)));
				aeyBean.setBbp_Gender(rs.getInt(6));
				aeyBean.setBbp_Photo(rs.getString(7));
				list.add(aeyBean);
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return list.toArray(new AdminExeBean[0]);
	}
	
	public void mappingDelet(int id){
		conn = connector.getConnection();
		
		try{
			
			String sql = "delete from MAPPING where Map_Young = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public void babyPhotoDelet(int id){
		conn = connector.getConnection();
		
		try{
			
			String sql = "delete from BABYPHOTO where Bbp_Members = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}

}
